//
//  LDSqlData.m
//  Gift
//
//  Created by 李迪 on 15/11/25.
//  Copyright © 2015年 dllo. All rights reserved.
//

#import "LDSqlData.h"
#import "GiftModel.h"
#import <sqlite3.h>

@interface LDSqlData ()

@end

@implementation LDSqlData


static sqlite3 *db;
#pragma mark - 单例方法的实现

+ (instancetype) sharedDataBase {
#pragma mark 创建单例这么创建.你那么创建有问题  要用GCD的创建
    static LDSqlData *dataTool = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dataTool = [[LDSqlData alloc] init];
        
    });
    return dataTool;
}

#pragma mark - 打开数据库

- (void) openDB {
    if (db != nil) {
//        NSLog(@"数据库已经打开");
        return;
    }
    
    // 数据库文件路径
    
    NSString *dbFile = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"model.db"];
    
    int result = sqlite3_open(dbFile.UTF8String, &db);
    
    if (result == SQLITE_OK) {
//        NSLog(@"打开数据库成功");
    } else {
        NSLog(@"打开数据库失败");
//        NSLog(@"%d", result);
    }
    
//    NSLog(@"%@", NSHomeDirectory());
    
}

#pragma mark - 创建表格

- (void) createTable {
    NSString *createSQL = @"create table if not exists collect (keyid integer primary key autoincrement, title text, cover_image_url text, content_url text)";
    int result = sqlite3_exec(db, createSQL.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"创建表格成功");
    } else {
        NSLog(@"创建表格失败");
//        NSLog(@"%d", result);
    }
    
}


#pragma mark - 查询

- (NSMutableArray *) selectCollectWithTitle:(NSString *)title {
    // 查询结果放在数组中
    NSMutableArray *arr = [NSMutableArray array];
    
    // sql
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM collect WHERE title = '%@'",title];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {

        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *cover_image_url = sqlite3_column_text(stmt, 2);
            const unsigned char *content_url = sqlite3_column_text(stmt, 3);
            
            GiftModel *model = [[GiftModel alloc] init];
            model.title = [NSString stringWithUTF8String:(const char *)title];
            model.cover_image_url = [NSString stringWithUTF8String:(const char *)cover_image_url];
            model.content_url = [NSString stringWithUTF8String:(const char *)content_url];
            [arr addObject:model];
        }
        sqlite3_finalize(stmt);
    } else {
        NSLog(@"无法查询");
        NSLog(@"%d", result);
        sqlite3_finalize(stmt);
    }
    
    return arr;
}



#pragma mark - 插入数据
- (void)insertCollect:(GiftModel *)model {
    
    NSString *insertSQL = [NSString stringWithFormat:@"insert into collect (title , cover_image_url , content_url) values('%@','%@','%@')", model.title, model.cover_image_url, model.content_url];
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
    } else {
        NSLog(@"插入数据失败");
        NSLog(@"%d", result);
    }
}

#pragma mark - 删除数据
- (void)deleteCollectWithTitle:(NSString *)title {
    NSString *deletSQL = [NSString stringWithFormat:@"DELETE FROM collect WHERE title = '%@'",title];
    
    int result = sqlite3_exec(db, deletSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
//        NSLog(@"删除数据成功");
    } else {
        NSLog(@"删除数据失败");
        NSLog(@"%d", result);
    }
}

#pragma mark - 关闭数据库
- (void)closeDB {
    
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
//        NSLog(@"关闭数据库成功");
        db = nil;
    } else {
        NSLog(@"关闭数据库失败");
        NSLog(@"%d", result);
    }
}


- (NSMutableArray *)selecteCollect {
    NSMutableArray *arr = [NSMutableArray array];
    NSString *selectSQL = @"SELECT * FROM collect";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
//        NSLog(@"查询中......");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            
            const unsigned char *cover_image_url = sqlite3_column_text(stmt, 2);
            
            const unsigned char *content_url = sqlite3_column_text(stmt, 3);
            
            GiftModel *model = [[GiftModel alloc] init];
            
            model.title = [NSString stringWithUTF8String:(const char *) title];
            
            model.cover_image_url = [NSString stringWithUTF8String:(const char *) cover_image_url];
            
            model.content_url = [NSString stringWithUTF8String:(const char *) content_url];
            [arr addObject:model];
        }
        
        sqlite3_finalize(stmt);
    } else {
        NSLog(@"无法查询");
        NSLog(@"%d", result);
        sqlite3_finalize(stmt);
    }
    return arr;
}




@end
